from day20.models.models_stu import Stu
from day20.models.models_score import Score
from day20.models.models_emp import Emp
from sqlalchemy.orm import Session, joinedload
from fastapi import HTTPException
from pydantic import BaseModel
from typing import Optional
from sqlalchemy import func

# 1.查询所有超过30岁的学员的信息
def get_stu_all_age_above_30(db:Session):
    return db.query(Stu).filter(Stu.age > 30).all()


# 2.统计每个班级男⽣⼥⽣的⼈数
def get_stu_all_gender_count(db:Session):
    result = (db.query(
        Stu.class_name,
        Stu.gender,
        func.count(Stu.stu_id).label("count")
        ).group_by(Stu.class_name, Stu.gender)
        .all())

    # 格式化结果为字典
    return [
            {
                "班级": item.class_name
                , "性别": item.gender
                , "人数": item.count
            }
            for item in result
            ]

#3. 查询每次考试成绩都在80分以上的学⽣的编号，姓名和成绩
def get_stu_all_scores_above_80(db: Session):
    # 子查询：找出“有任意一次成绩≤80”的学生ID（需要排除的学生）
    excluded_stu_ids = (db.query(Score.stu_id)
                        .filter(Score.mark <= 80)
                        .distinct().subquery())


    result = ((db.query(
        Stu.stu_id,
        Stu.stu_name,
        Score.assessment_sequence,  # 考核序次
        Score.mark.label("score")  # 具体成绩
        ).join(Score, Stu.stu_id == Score.stu_id)# 关联学生表和成绩表，若是有冗余数据，可以将Stu模型直接换成Score，不用再进行连接查询
    .filter(
            Stu.stu_id.notin_(excluded_stu_ids),  # 排除有成绩≤80的学生，notin_(...)等同于sql中not in
            Score.mark > 80  # 确保只返回>80的成绩
        ))
              .order_by(Stu.stu_id, Score.assessment_sequence)).all()
    return [
            {
                "学生编号": item.stu_id,
                "姓名": item.stu_name,
                "考核序次": item.assessment_sequence,
                "成绩": item.score
            }
            for item in result
            ]


#4. 查询有两次以上不及格的学⽣的姓名，班级和不及格成绩
def get_stu_all_scores_fail_2(db: Session):
    qualified_stu_ids = (db.query(Score.stu_id)
                         .filter(Score.mark < 60)
                         .group_by(Score.stu_id)
                         .having(func.count(Score.stu_id) >=2 )
                         .subquery())

    result = (((db.query(
        Stu.stu_name,
        Stu.class_name,
        Score.mark.label("score")  # 具体成绩
        ).join(Score, Stu.stu_id == Score.stu_id)
    .filter(
            Stu.stu_id.in_(qualified_stu_ids),
            Score.mark < 60
        ))
              .order_by(Stu.stu_name,Stu.class_name))
              .all())

    return [
            {"姓名": item.stu_name, "班级": item.class_name, "不及格成绩": item.score}
            for item in result
            ]

# 5.统计每次考试每个班级的平均分，按照从⾼到低排序
def get_class_avg_score_rank(db:Session):
    result = (((db.query(Score.assessment_sequence
                      , Stu.class_name
                      , func.avg(Score.mark).label("avg_score")
             ).join(Stu,Score.stu_id==Stu.stu_id)
              .group_by(Score.assessment_sequence, Stu.class_name))
              .order_by(Score.assessment_sequence, func.avg(Score.mark).desc()))
              .all())
    return [{"考核序次":item.assessment_sequence
                ,"学生班级":item.class_name
                ,"平均成绩":item.avg_score
             }
            for item in result
            ]


# 6.统计就业薪资最⾼的前五名学⽣的姓名，班级和就业时间，就业公司
def get_stu_max_sal_5(db:Session):
    result = ((db.query(Stu.stu_name
                      ,Stu.class_name
                      ,Emp.offer_issue_time
                      ,Emp.emp_company_name
                      ,Emp.emp_salary
             ).join(Emp,Stu.stu_id==Emp.stu_id)
              .order_by(Emp.emp_salary.desc())
              .limit(5))
              .all())

    return [
            {"姓名":item.stu_name
              ,"学生班级":item.class_name
              ,"就业时间":item.offer_issue_time
              ,"就业公司":item.emp_company_name
             }
            for item in result
            ]

# 7.统计每个学⽣的就业时⻓（offer下发时间-就业开放时间）
def get_all_stu_offer_issue_time(db:Session):
    result = (db.query(Emp.stu_id
                      ,func.datediff(Emp.offer_issue_time,Emp.emp_open_time).label("就业时长")
                      )
              .all())

    return [{
              "学生编号":item.stu_id
              ,"就业时长":item.就业时长
            }
            for item in result
            ]

# 8.统计每个班级的平均就业时⻓（只统计进⼊就业阶段的学⽣，也就是有就业开放时间）
def get_class_avg_offer_issue_time(db:Session):
    result = ((db.query(Stu.class_name
                      ,func.avg(func.datediff(Emp.offer_issue_time,Emp.emp_open_time)).label("就业时长")
                      ).join(Stu,Emp.stu_id==Stu.stu_id)
              .group_by(Stu.class_name))
              .all())

    return [
            {"学生班级":item.class_name
              ,"平均就业时长":item.就业时长
             }
            for item in result
            ]